package database.dbObjects;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import main.Main;
import objects.Lesson;
import database.dbObjects.IDBCourse.DBCourse;

public interface IDBLesson extends IDBEvent {
	
	public class DBLesson extends DBEvent {
		
		/*
		 * methods are final for optimization of the code (by the compiler)
		 * it is not possible anymore to inherit it after this declaration 
		 */
		
		public final void init() throws Exception {
			
			createTable();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table Lesson( " +
						   "Start char(" + lengthDateTimeFormat + ") not null, " +
						   "Summary char(" + maxLengthSummary + ") null, " +
						   "Room varchar(" + maxLengthRoom + ") not null, " +
						   "DayOfWeek integer not null, " +
						   "NumberOfWeeks integer not null, " +
						   "Course_Name varchar(" + DBCourse.maxLengthName + ") not null, " +
						   "foreign key (Start) references Event (Start), " +
						   "foreign key (Summary) references Event (Summary), " +
						   "foreign key (Course_Name) references Course (Name), " +
						   "primary key (Start, Summary) " +
						       "on conflict abort, " +
						   "check (DayOfWeek between 1 and 7), " +
						   "check (NumberOfWeeks > 0));";
			
			executeCreation(query);
		}
		
		public final boolean add(String start, String end, String summary, 
				String warn, String moreInfo, String room, Integer day, 
				Integer weeks, String courseName) throws Exception {
			
			checkConsistance(start, end, summary, warn, moreInfo, room, 
					day, weeks, courseName);
			
			boolean success = super.add(start, end, summary, false, warn, moreInfo);
			
			Vector<Object> v = new Vector<Object>(6);
			v.add(0, start);
			v.add(1, summary);
			v.add(2, room);
			v.add(3, day);
			v.add(4, weeks);
			v.add(5, courseName);
			
			return (success && super.add("Lesson", v));
		}
		
		public final boolean updateRoom(String start, String summary, 
				String newRoom) throws Exception {
			
			checkKeys(start, summary);
			checkRoom(newRoom);
			
			Vector<Object> keys = new Vector<Object>(2);
			keys.add(0, start);
			keys.add(1, summary);
			
			return super.update("Lesson", keyColumns, keys, "Room", newRoom);
		}
		
		public final boolean updateDayOfWeek(String start, String summary, 
				Integer newDay) throws Exception {
			
			checkKeys(start, summary);
			checkDayOfWeek(newDay);
			
			Vector<Object> keys = new Vector<Object>(2);
			keys.add(0, start);
			keys.add(1, summary);
			
			return super.update("Lesson", keyColumns, keys, "DayOfWeek", newDay);
		}
		
		public final boolean updateNumberOfWeeks(String start, String summary, 
				Integer newWeeks) throws Exception {
			
			checkKeys(start, summary);
			checkNumberOfWeeks(newWeeks);
			
			Vector<Object> keys = new Vector<Object>(2);
			keys.add(0, start);
			keys.add(1, summary);
			
			return super.update("Lesson", keyColumns, keys, "NumberOfWeeks", newWeeks);
		}
		
		public final boolean updateCourseName(String start, String summary, 
				String newCourseName) throws Exception {
			
			checkKeys(start, summary);
			DBCourse.checkName(newCourseName);
			
			Vector<Object> keys = new Vector<Object>(2);
			keys.add(0, start);
			keys.add(1, summary);
			
			return super.update("Lesson", keyColumns, keys, "Course_Name", newCourseName);
		}
		
		public Lesson getLesson(String start, String summary) throws Exception {
			// Returns the requested object or null if nothing was found
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Lesson natural join Event " +
									"where ((Lesson.Start = '" + start + "') and " +
									"(Lesson.Summary = '" + summary + "'));");
			if (rs.next()) {
				Lesson l = new Lesson(rs.getString("Start"), 
						rs.getString("End"), rs.getString("Summary"), 
						rs.getString("WarningDate"), rs.getString("MoreInformation"), 
						rs.getString("Room"), rs.getInt("DayOfWeek"), 
						rs.getInt("NumberOfWeeks"), rs.getString("Course_Name"));
				rs.close();
				return l;
			}
			rs.close();
			return null;
		}
		
		public Vector<Lesson> getAllLessons() throws Exception {
			
			Statement stat = Main.connection.createStatement();
			
			ResultSet rs = stat.executeQuery("select * from Lesson natural join Event;");
			
			Vector<Lesson> v = new Vector<Lesson>();
			int counter = 0;
			while (rs.next()) {
				v.add(counter, new Lesson(rs.getString("Start"), 
					rs.getString("End"), rs.getString("Summary"), rs.getString("WarningDate"), 
					rs.getString("MoreInformation"), rs.getString("Room"), 
					rs.getInt("DayOfWeek"), rs.getInt("NumberOfWeeks"), 
					rs.getString("Course_Name")));
				counter++;
			}
			rs.close();
			return v;
		}
		
		private final void checkConsistance(String start, String end, String summary, 
				String warn, String moreInfo, String room, Integer day, 
				Integer weeks, String courseName) throws Exception {
			
			super.checkConsistance(start, end, summary, false, warn, moreInfo);
			checkRoom(room);
			checkDayOfWeek(day);
			checkNumberOfWeeks(weeks);
			DBCourse.checkName(courseName);
		}
		
		private final void checkRoom(String room) throws Exception {
			
			if (room == null) { throw (new Exception("Room needed!")); }
			if (room.length() < 1) {
				throw (new Exception("Room has to be at least 1 character long!"));
			}
			if (room.length() > maxLengthRoom) {
				throw (new Exception("Room exceeds maximum length (" +
						maxLengthRoom + " characters)!"));
			}
		}
		
		private final void checkDayOfWeek(Integer day) throws Exception {
			
			if (day == null) { throw (new Exception("Day of week needed!")); }
			if (!((day >= 1) && (day <= 7))) { 
				throw (new Exception("Day of week has to be between 1 and 7, " +
						"equivalent to the corresponding day of the week)!"));
			}
		}
		
		private final void checkNumberOfWeeks(Integer weeks) throws Exception {
			
			if (weeks == null) { throw (new Exception("Number of weeks needed!")); }
			if (weeks < 1) {
				throw (new Exception("Number of weeks has to be equal or more than 1!"));
			}
			if (weeks > maxNrOfWeeks) {
				throw (new Exception("Number of weeks exceeds maximum (" +
						maxNrOfWeeks + " weeks)!"));
			}
		}
	/** TODO wegdoen??	
		protected final void checkCourseName(String courseName) throws Exception {
			
			if (courseName == null) { throw (new Exception("Course name needed!")); }
			if (courseName.length() < 1) {
				throw (new Exception("Course name has to be at least 1 character long!"));
			}
			if (courseName.length() > DBCourse.maxLengthName) {
				throw (new Exception("Course name exceeds maximum length (" +
						DBCourse.maxLengthName + " characters)!"));
			}
		}
		
	*/	
		private final int maxLengthRoom = 5;
		private final int maxNrOfWeeks = 13;
		
	}
	
}
